"""
Case Type   : xml类型
Case Name   : gs_dump/gs_restore备份/恢复包含xml类型值的表(tar归档格式)，恢复后xml值不变
Create At   : 2022/08/29
Owner       : songjing20
Description :
    1.创建表,插入数据
    2.查询表
    3.gs_dump备份表,tar格式
    4.gs_restore恢复表
    5.查询表数据
    6.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.与2结果一致
    6.成功
History     :
"""

import os
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class XmlDatatype(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'Xml_0052.tar')
        self.table = 't_xml_0052'

    def test_xml_datatype(self):
        text = '-----step1:创建表,插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.table} cascade;
        create table {self.table}(c_int int, c_xml xml)
        partition by range(c_int) (
          partition p1 values less than(100),
          partition p2 values less than(1000),
          partition p3 values less than(maxvalue));
        insert into {self.table} values(50, xml'<value>one</value>');
        insert into {self.table} values(666, xmlelement(
        name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar)));
        insert into {self.table} values(2000, 
        xml'<?xml version=\\"1.0\\" encoding=\\"UTF-8\\"?>
<ROOT>
    <!-- 整体信息 -->
    <CLUSTER>
        <!-- 数据库名称 -->
        <PARAM name=\\"clusterName\\" value=\\"cluster01\\" />
        <!-- 数据库节点名称(hostname1,2) -->
        <PARAM name=\\"nodeNames\\" value=\\"hostname1\\" />
    </CLUSTER>
</ROOT>');'''
        sql_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, sql_res,
                      f'执行失败:{text}')
        self.assertEqual(sql_res.count(self.constant.INSERT_SUCCESS_MSG), 3,
                         f'执行失败:{text}')

        text = '-----step2:查询表;expect:成功，3条数据-----'
        self.log.info(text)
        select_sql = f"select * from {self.table} order by 1;"
        select_res = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(select_res)
        self.assertIn('''50 | <value>one</value>
   666 | <foo bar="2009-04-09T00:24:37"/>
  2000 | <ROOT>                                                +
       |     <!-- 整体信息 -->                             +
       |     <CLUSTER>                                         +
       |         <!-- 数据库名称 -->                      +
       |         <PARAM name="clusterName" value="cluster01" />+
       |         <!-- 数据库节点名称(hostname1,2) -->   +
       |         <PARAM name="nodeNames" value="hostname1" />  +
       |     </CLUSTER>                                        +
       | </ROOT>
(3 rows)''', select_res, f'执行失败:{text}')

        text = '-----step3:gs_dump备份表,tar格式; expect:成功-----'
        self.log.info(text)
        dump_res = self.pri_sh.exec_gs_dump(
            self.dump_path, f'-t {self.table} -F t', get_detail=False)
        self.assertTrue(dump_res, f'执行失败:{text}')

        text = '-----step4:gs_resotre恢复表; expect:成功-----'
        self.log.info(text)
        self.log.info('-----删除部分表数据-----')
        res = self.pri_sh.execut_db_sql(
            f'delete from {self.table} where c_int > 500;')
        self.log.info(res)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG,
                      res, f'delete失败:{text}')
        self.log.info('-----gs_restore恢复数据-----')
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_restore -d {self.pri_node.db_name} " \
              f"-p {self.pri_node.db_port} -c {self.dump_path}"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_node, cmd)
        self.assertIn(self.constant.RESTORE_SUCCESS_MSG, res, f'恢复数据失败:{text}')

        text = '-----step5:查询表数据;expect:与step2结果一致-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(res)
        self.assertEqual(select_res, res, f'执行失败:{text}')

    def tearDown(self):
        text = '-----step6:清理环境;expect:成功-----'
        self.log.info(text)
        drop_res = self.pri_sh.execut_db_sql(
            f"drop table if exists {self.table};")
        self.log.info(drop_res)
        cmd = f'rm -f {self.dump_path};' \
              f'if [ -f {self.dump_path} ]; ' \
              f'then echo "exists"; else echo "not exists"; fi'
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_node, cmd)
        self.assertEqual('not exists', res, f'删除文件失败:{text}')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_res, f'删表失败:{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
